SET ROLE

SET ROLE — Set the session user identifier and the current user identifier of the current session

Synopsis

SET [ SESSION | LOCAL ] ROLE role_name

SET [ SESSION | LOCAL ] ROLE NONE

RESET ROLE

Description

This command sets the current user identifier of the current SQL session to role_name. The role name can be written as an identifier or a string. After SET ROLE, SQL command privilege checks proceed as if the specified role were the one originally logged in.

The current session user must be a member of the specified role role_name (if the session user is a superuser, any role can be selected).

The SESSION and LOCAL modifiers function the same as for the regular SET command.

The NONE and RESET forms reset the current user identifier to the current session user identifier. These forms can be executed by any user.

Notes

Using this command can either increase or restrict privileges. If the session user role has the INHERIT attribute, it automatically has all privileges of all roles it can SET ROLE to. In this case, SET ROLE effectively removes all privileges directly assigned to the session user and privileges of other roles the session user is a member of, leaving only the privileges available to the mentioned role. In other words, if the session user does not have the NOINHERIT attribute, SET ROLE drops the privileges directly assigned to the session user and obtains the privileges available to the mentioned role.

In particular, when a superuser chooses to SET ROLE to a non-superuser role, they lose their superuser privileges.

The effect of SET ROLE is comparable to SET SESSION AUTHORIZATION, but the privilege checks involved are completely different. Also, SET SESSION AUTHORIZATION determines which roles can be used by subsequent SET ROLE commands, but changing the role with SET ROLE does not change the set of roles available for subsequent SET ROLE.

SET ROLE does not process session variables set by the role's ALTER ROLE settings. This only happens during login.

SET ROLE cannot be used inside a SECURITY DEFINER function.

Examples

SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--------------+--------------

peter | peter

SET ROLE 'paul';

SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--------------+--------------

peter | paul

See Also

SET SESSION AUTHORIZATION